<?php
/*
 * Session variables:
 * $_SESSION['id'] [serial] = id of temporary member who verified his/her account
 * $_SESSION['message'] [string] = message prompting the user whether account is created
 * $_SESSION['new_db'] [boolean] = indicator that new database is created
 * $_SESSION['type'] [string] = user type of the logged in user
 * $_SESSION['authorised'] [boolean] = whether or not the current user is logged in
 * $_SESSION['user'] [string] = the username/ email of the logged in user
 */
	session_start();
    pg_connect("host=localhost user=postgres password='password' dbname=tbms_databases") or die(ps_last_error());
    $id=$_SESSION['id'];	
	
	$sql="SELECT * FROM temp_members WHERE temp_member_id='$id'";
	$result=pg_query($sql);
    $rows=pg_fetch_array($result);
	$username=$rows['username'];
	$lastname=$rows['lastname'];
	$firstname=$rows['firstname'];
	$middlename=$rows['middlename'];
	$email=$rows['email'];
	$address1=$rows['stadd1'];
	$address2=$rows['stadd2'];
	$city=$rows['city'];
	$gender=$rows['gender'];
	$birthday=$rows['birthday'];
	$contact_num=$rows['contact_num'];
	$password=$rows['password'];
	$centre=$rows['tutorial_centre'];
   
    $sql1="SELECT * FROM databases where tutorial_centre = '$centre'";
	$result1 = pg_query($sql1);
    $count = pg_num_rows($result1);    
    if($count >= 1)
    {
        $sql3="DELETE FROM temp_members WHERE temp_member_id='$id'";
		$result3=pg_query($sql3);
		$_SESSION['message']='Tutorial Centre already exists.';
        header('Location: verify.php');
        exit();
    }
    else
    {
        $sql3="DELETE FROM temp_members WHERE temp_member_id='$id'";
		$result3=pg_query($sql3);
		$sql="insert into databases (tutorial_centre) values ('$centre')";
        $result = pg_query($sql);
        
        $sql2="select database_id from databases where tutorial_centre = '$centre'";
        $result2 = pg_query($sql2);
		
        $dbid = "db".pg_fetch_result($result2, 0, 'database_id');
        
        $sql3 = "create database $dbid;";
        $result3 = pg_query($sql3);
		
        pg_connect("host=localhost user=postgres password='password' dbname=$dbid") or die(pg_last_error());
        
		$sql = "CREATE TABLE users
        (
            username varchar(100) primary key not null,
            password varchar(30) not null,
            usertype varchar(15) not null,
			firstName varchar(100) not null,
            middleName varchar(100) not null,
            lastName varchar(100) not null,
			gender varchar(10) not null,
			birthday date not null,
			contactNum varchar(15) not null,
            streetAdd1 varchar(100) not null,
            streetAdd2 varchar(100) not null,
            cityAdd varchar(100) not null
        )";
        $result = pg_query($sql);
				
		$sql="INSERT INTO users (username, password, usertype, lastname, firstname, middlename, streetAdd1, streetAdd2, cityAdd, contactNum, gender, birthday) VALUES ('$email','$password', 'administrator', '$lastname','$firstname','$middlename', '$address1','$address2','$city','$contact_num', '$gender', '$birthday')";
		$result=pg_query($sql);
        
		
        $sql = "CREATE TABLE buildings
		(
			building_id serial primary key,
			building_name varchar(100),
			num_floors integer,
			zipcode integer,
			contactnum integer,
			province varchar(100),
			city_or_town varchar(100),
			stadd1 varchar(100),
			stadd2 varchar(100)
		)";
		$result = pg_query($sql);
		
        $sql = "CREATE TABLE rooms
		(
			room_id serial primary key,
			room_name varchar(100),
			floor integer,
			capacity integer,
			room_number integer,
			building_id integer references buildings on delete cascade
		)";
		$result = pg_query($sql);
			
		$sql = "CREATE TABLE parents
        (
			parent_id serial primary key,
			username varchar(100) references users on delete cascade
        )";
        $result = pg_query($sql);
		
	$sql = "CREATE TABLE students
        (
            student_id serial primary key,
	    parent_id integer references parents on delete set null,
            date_enrolled date not null,
            grade_level varchar(100) not null,
            school varchar(100) not null,
            is_current boolean default false,
            is_in boolean default true,
            num_hours interval default interval '0 hours', 
            mode varchar(15) default 'PER HOUR',
            per_mode numeric(16,2),
	    num_session integer default 0,
	    time_in timestamp,
	    payment numeric(16,2),
            username varchar(100) references users on delete cascade
        )";
        $result = pg_query($sql);
        
		$sql = "CREATE TABLE teachers
        (
			teacher_id serial primary key,
			username varchar(100) references users on delete cascade,
			emergency_contact_person varchar(150),
			emergency_contact_number integer,
			is_available boolean default true,
			is_in boolean default true,
			mode varchar(15) default 'PER HOUR',
			num_hours interval default interval '0 hours', 
			num_session integer default 0,
			teach_time timestamp,
			per_mode numeric(16,2),
			salary numeric(16,2),
			educational_attainment varchar(100) 
	    )";
        $result = pg_query($sql);
        
        $sql4 = "CREATE TABLE classes
        (
			class_id serial primary key,
			subject varchar(100),
			section varchar(100),
			room_id integer references rooms on delete cascade,
			starttime varchar(10),
			endtime varchar(10),
			startdate date,
			enddate date,
			teacher_id integer references teachers on delete cascade,
			num_students integer default 0,
			for_reservation boolean default true
        )";
        $result = pg_query($sql4);
		
        $sql = "CREATE TABLE enrolled_at
        (
			class_id integer references classes on delete cascade,
			student_id integer references students on delete cascade
	    )";
        $result = pg_query($sql);
        
        $sql = "CREATE TABLE feedbacks
        (
            feedback_id serial primary key,
            feedback_date timestamp not null,
            username varchar(30) references users on delete cascade,
            feedback varchar(1000)
        )";
        $result = pg_query($sql);
		
        $sql = "CREATE TABLE transactions
        (
            transaction_id serial primary key,
            transaction_date timestamp not null,
            username varchar(100) references users on delete cascade,
            amount numeric(16,2),
            paid boolean default false,
            description varchar(250)
        )";
        $result = pg_query($sql);
        
        $sql = "CREATE TABLE announcements
        (
            announcement_id serial primary key,
            announcement_date timestamp not null,
            announcement_title varchar(100) not null,
            announcement varchar(1000)
        )";
        $result = pg_query($sql);
		
        $sql = "CREATE TABLE tutor_evaluations
        (
        	evaluation_id serial primary key,
        	teacher_id integer references teachers on delete cascade,
        	audibility integer,
        	manner_of_teaching integer,
        	subject_mastery integer,
        	materials_used integer,
        	preparedness integer,
        	feedback varchar(1000)
        )";
        $result = pg_query($sql);
        
        $sql = "CREATE TABLE student_evaluations
        (
        	eval_id serial primary key,
        	eval_date timestamp not null,
        	teacher_id integer references teachers on delete cascade,
        	student_id integer references students on delete cascade,
        	evaluation varchar(10000)
        )";
        $result = pg_query($sql);
        
        $sql = "create table reservations (
			reservation_id serial primary key,
			reservation_date timestamp not null,
			username varchar(100) references users on delete cascade,
			class_id integer references classes on delete cascade)";
        $result = pg_query($sql);
        
		$_SESSION['message'] = "Your account has been activated. Please login.";
        $_SESSION['newdb'] = true;
        header('Location: verify.php');
        exit();
    }   
?>
